﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ANZ_AIM_DAL;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;

namespace ANZ_AIM_BLL
{
    public class BatchFileChangeUploadBLL
    {
        private string ConnectionString;
        private const string TABLE_NAME = "BatchFileChangeUploads";
        private Dictionary<string, string> ObjectPropDict;

        public BatchFileChangeUploadBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void Save(BatchFileChangeUpload batchFileChangeUpload, ref int IDReturn)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                command = CommandHelper.BuildInsertCommand(batchFileChangeUpload, dict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, ref IDReturn, parameters);
            }
            catch (Exception ex) { throw ex; }
        }

        public void UpdateStatus(string batchFileChangeID, string status)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                dict.Remove("BatchFileChangeID");
                dict.Remove("FileName");
                dict.Remove("TotalRecord");
                dict.Remove("CreatedDate");
                BatchFileChangeUpload batchFileChangeUpload = new BatchFileChangeUpload() { Status = status, BatchFileChangeID = batchFileChangeID, IsSentMail = "No" };
                OleDbParameter[] parameters;
                string command = string.Empty;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"}
                    };
                command = CommandHelper.BuildUpdateCommand(batchFileChangeUpload, dict, conditionDict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }

        }


        public void SetMailSent(string batchFileChangeID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                dict.Remove("BatchFileChangeID");
                dict.Remove("FileName");
                dict.Remove("TotalRecord");
                dict.Remove("CreatedDate");
                dict.Remove("Status");
                BatchFileChangeUpload batchFileChangeUpload = new BatchFileChangeUpload() { IsSentMail = "Yes", BatchFileChangeID = batchFileChangeID };
                OleDbParameter[] parameters;
                string command = string.Empty;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"}
                    };
                command = CommandHelper.BuildUpdateCommand(batchFileChangeUpload, dict, conditionDict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }

        }
        public DataTable GetBatchFileByStatus(string status)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                BatchFileChangeUpload batchFileChangeUpload = new BatchFileChangeUpload { Status = status };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"Status", "Status"}
                    };
                string command = CommandHelper.BuildSelectCommand(batchFileChangeUpload, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public DataTable Search(string status, string batchFile, string fromDate, string toDate)
        {
            try
            {
                string command = string.Format("SELECT [BatchFileChangeID], [FileName], [TotalRecord], [CreatedDate], [Status] FROM [{0}] WHERE (@BatchFileChangeID IS NULL OR BatchFileChangeID=@BatchFileChangeID) AND (@Status IS NULL OR Status=@Status) AND CreatedDate BETWEEN @FromDate AND @ToDate;", TABLE_NAME);
                OleDbParameter[] parameters = new OleDbParameter[4];
                if (string.IsNullOrEmpty(batchFile))
                {
                    parameters[0] = new OleDbParameter("@BatchFileChangeID", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@BatchFileChangeID", batchFile);
                }
                parameters[1] = new OleDbParameter("@Status", status);
                parameters[2] = new OleDbParameter("@FromDate", fromDate);
                parameters[3] = new OleDbParameter("@ToDate", toDate);

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public void Delete(string batchFileChangeID)
        {
            try
            {
                var dict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"}
                    };
                BatchFileChangeUpload batchFileChangeUpload = new BatchFileChangeUpload() { BatchFileChangeID = batchFileChangeID };
                OleDbParameter[] parameters;
                string command = string.Empty;
                command = CommandHelper.BuildDeleteCommand(batchFileChangeUpload, dict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }

        }
        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"BatchFileChangeID", "BatchFileChangeID"},
                {"FileName", "FileName"},
                {"TotalRecord", "TotalRecord"},
                {"CreatedDate", "CreatedDate"},
                {"Status", "Status"},
                {"IsSentMail", "IsSentMail"}
            };

            return dictionary;
        }
    }
}
